OpenEdge Data Management:<br />SQL Development Referential constraints

Relational databases often contain tables that have identical columns. To maintain referential integrity, the data in the columns must remain consistent. Referential constraints ensure this integrity.

In Example 5–20, the value in the item_no column of the supplier_item table depends on the value in the item_no column of the item table. The item_no column of the supplier_item table references the item_no column of the item table. The item_no column is a foreign key in the supplier_item table.

Example 5–20: Referential constraint
CREATE TABLE supplier_item ( suppl_no INTEGER NOT NULL PRIMARY KEY, item_no INTEGER REFERENCES item (item_no), quantity INTEGER ) ;

Foreign key constraint

A foreign key is a column that references a primary key of another table. The foreign key value either is NULL or exists as the primary key value. The table that contains the foreign key is called the referencing table. The table that contains the primary key is called the referenced table.

During INSERT or UPDATE operations on a table containing a foreign key, the database checks to determine if the foreign key value matches a corresponding primary key value. If it does not match, the operation returns an error.

During UPDATE or DELETE operations on a table containing a primary or candidate key, if the values to be deleted or updated match the foreign key of the referencing table, the operation returns an error. A value corresponding to a primary or candidate key cannot be updated or deleted if there are references to it.

When you want to drop a table containing a primary or candidate key, the database checks to see if the table has any references to it. If there are tables containing foreign keys that reference the primary or candidate keys of the table you want to drop, the operation returns an error.

In Example 5–21, item_no is the foreign key referencing the item table, and the foreign key is specified at the column level.

Example 5–21: Foreign key constraint
CREATE TABLE supplier_item ( supp_no INTEGER NOT NULL PRIMARY KEY, item_no INTEGER NOT NULL REFERENCES item, qty INTEGER ) ;

If a foreign key references a candidate key, you must name the referenced column in a column list. If a foreign key references a primary key, the column list is optional.

Example 5–22 illustrates both conditions. In the example, invoice.item_no references the primary key of the item table. The invoice.partnum column references parts.part_no. Since parts.part_no is a primary key, the parts (part_no) column list reference in invoice.part_no is optional.

Example 5–22: Referential and foreign key constraints
CREATE TABLE invoice ( inv_no INTEGER NOT NULL PRIMARY KEY, item_no INTEGER REFERENCES item, part_no CHAR(3) NOT NULL REFERENCES parts (part_no), qty INTEGER NOT NULL ) ;